﻿using Dapper;
using Oracle.ManagedDataAccess.Client;
using SocketPostData.BLL;
using SocketPostData.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SocketPostData.DAL
{
    public class DBHelper
    {
        // Methods
        public static List<real_sbgk_sjjgb> GetRealDcsDataList()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string sql = $@"select * from real_sbgk_sjjgb a";
                var res = conn.Query<real_sbgk_sjjgb>(sql).ToList();
                return res;
            }
        }

        public static List<realwastegasdata> GetRealSiteDataList()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string sql = $@"select * from realwastegasdata where netstatus <> 0";
                var res = conn.Query<realwastegasdata>(sql).ToList();
                return res;
            }
        }

        public static List<sbgk_sjtbpzb> GetSyncDataList()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string sql = $@"select * from sbgk_sjtbpzb a order by id";
                var res = conn.Query<sbgk_sjtbpzb>(sql).ToList();
                return res;
            }
        }

        public static List<sbgk_sjtbpzxxb> GetDcsArgInfoList()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string sql = $@"select a.*,
                                   (select max(sjtbpzb_hsxs)
                                      from sbgk_sjtbpzb b
                                     where b.sjtbpzb_sbid = a.sjtbpzb_sbid
                                       and b.sjtbpzb_csbm = a.sjtbpzb_csbm) sjtbpzxxb_hsxs
                              from sbgk_sjtbpzxxb a
                             order by id";
                var res = conn.Query<sbgk_sjtbpzxxb>(sql).ToList();
                return res;
            }
        }

        /// <summary>
        /// 获取工况参数列表
        /// </summary>
        /// <returns></returns>
        public static List<sbgk_sbcsxxb> GetEquipmentArgList(bool issecondData = true)
        {
            using (var conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                DynamicParameters pars = new DynamicParameters();

                string where = "";
                if (issecondData)
                {
                    where = "and issecondData=1";
                }

                string sql = $@"select * from sbgk_sbcsxxb where delflag=0 {where}";
                var res = conn.Query<sbgk_sbcsxxb>(sql, pars).ToList();
                return res;
            }
        }

        /// <summary>
        /// 获取工况参数列表
        /// </summary>
        /// <returns></returns>
        public static List<sbgk_sjtbpzb_sum> GetDcsSumArgList()
        {
            using (var conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                DynamicParameters pars = new DynamicParameters();

                string sql = $@"select * from sbgk_sjtbpzb_sum";
                var res = conn.Query<sbgk_sjtbpzb_sum>(sql, pars).ToList();
                return res;
            }
        }

        /// <summary>
        /// 获取秒级数据分钟最大值
        /// </summary>
        /// <returns></returns>
        public static List<real_dcs_sjjgbsecond> GetPreMinSecondData_Max()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string time = DateTime.Now.AddMinutes(-1).ToString("yyyy-MM-dd HH:mm")+":00";

                string sql = $@"select * from
                                  (select sbcsxxb_id,
                                   sbcsxxb_csdm,
                                   to_char(stime, 'yyyy-mm-dd hh24:mi') || ':00' stime,
                                   max(svalue) svalue
                              from his_dcs_sjjgbsecond_fiveday
                             where stime > (sysdate - 2 / 24 / 60)
                             group by sbcsxxb_id, sbcsxxb_csdm, to_char(stime, 'yyyy-mm-dd hh24:mi')
                            order by stime,sbcsxxb_id) where stime=:time";
                var res = conn.Query<real_dcs_sjjgbsecond>(sql,new { time }).ToList();
                return res;
            }
        }


        /// <summary>
        /// 获取秒级数据分钟最大值
        /// </summary>
        /// <returns></returns>
        public static List<real_islandsumdata> GetRealIslandDataList()
        {
            using (OracleConnection conn = new OracleConnection(Config.DBConnString))
            {
                conn.Open();

                string sql = $@"select * from real_islandsumdata a";
                var res = conn.Query<real_islandsumdata>(sql).ToList();
                return res;
            }
        }
    }
}
